#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
INSERT INTO edu_online_dwb.sign_up_wide
SELECT
--来自fac_customer_signed的字段
    fcs.id,
    substr(fcs.payment_time,1,4) AS dt_year,
    substr(fcs.payment_time,6,2) AS dt_month,
    substr(fcs.payment_time,9,2) AS dt_day,
    CASE WHEN fcs.origin_type = 'NETSERVICE' or fcs.origin_type = 'PRESIGNUP' THEN '线下' ELSE '线上' END,
    CASE WHEN payment_state = 'PAID' THEN payment_state ELSE NULL END,
--来自fac_source_channel的字段
    fsc.seo_source,
--来自fac_consulting_center的字段
    fcc.id AS \`dept_id\`,
    fcc.name AS \`dept_name\`,
--来自dim_campus_subject的字段
    dcs.itcast_school_id,
    dcs.itcast_school_name,
    dcs.itcast_subject_id,
    dcs.itcast_subject_name
FROM edu_online_dwd.fac_customer_signed fcs
         LEFT JOIN edu_online_dwd.dim_campus_subject dcs
                   ON fcs.itcast_clazz_id = dcs.id
         LEFT JOIN edu_online_dwd.fac_consulting_center fcc
                   ON fcs.creator = fcc.emp_id
         LEFT JOIN edu_online_dwd.fac_source_channel fsc
                   ON fcs.customer_id = fsc.customer_id;

INSERT INTO edu_online_dwb.fac_valid_clue
SELECT *
FROM edu_online_dwd.fac_valid_clue;
"